<?php
class PgSqlDao extends Dao {
    public $sequence = true;
    protected function __construct($connSetting, $connName=DEFAULT_CONN) {
        parent::__construct($connSetting);
        $this->setConn($connName);
    }

    protected function getDsn($connSetting) {
        $port = $connSetting['DBPORT'] ? $connSetting['DBPORT'] : 5432;
        return 'pgsql:host=' . $connSetting['DBHOST'] . ';dbname=' . $connSetting['DBNAME'] . ';port=' . $port;
    }

    public function getMetadata($tableName) {
        $tableName = strtolower($this->dbPrefix . $tableName);
        $columnArray = $this->getColumnArray($tableName);
        $tableKeys = $this->getTableKeys($tableName, $columnArray);
        $uniqueKeys = $tableKeys['uniqueKeys'];
        $notnullColumns = array();
        if (is_array($uniqueKeys)) {
            foreach ($uniqueKeys as $keyName => $columns) {
                $notnullColumns = array_merge($notnullColumns, $columns);
            }
            $notnullColumns = array_unique($notnullColumns);
        }
        
        $columns = array();
        foreach ($columnArray as $column) {
            $extra = 'PUMP_COL_TYPE_CUSTOM';
            $columnName = $column['column_name'];
            if(false !== stripos($column['column_type'], 'int')) {
                $columnLen = abs(trim($column['column_type'], 'int'));
                $intLenCfg = array('2'=>6, '4'=>11, '8'=>20);
                $columnType = 'Number(' . $intLenCfg[$columnLen] . ')';
                $columns[$columnName]['type'] = $columnType;
            } else if (preg_match('/numeric|float/i', $column['column_type'])) {
                if(-1 == $column['column_length']) $coulmnType = 'Number(15,4)';
                else {
                    $columnLen = $column['column_length'];
                    $columnType = 'Number(' . ($columnLen >>16) . ',' . (($columnLen-4) & 0xffff) . ')';
                }
                $columns[$columnName]['type'] = $columnType;
            } else if (preg_match('/char|text|tsvector/', $column['column_type'])) {
                $columnLen = $column['column_length'];
                if ($columnLen > 0) {
                    $columns[$columnName]['type'] = 'String(' . ($columnLen - 4) . ')';
                } else{
                    $columns[$columnName]['type'] = 'String';
                    if('tsvector' == $column['column_type']) {
                        $columns[$columnName]['fulltext']=true;
                    }
                }
            } else if (false !== preg_match('/date|timestamp/i', $column['column_type'])) {
                $columns[$columnName]['type'] = $column['column_type'] == 'date' ? 'date' : 'datetime';
            }
            
            $columns[$columnName]['notnull'] = $column['not_null'] == 'N' ? false : true;
            if(false == $columns[$columnName]['notnull'] && in_array($columnName, $notnullColumns)) {
                throw new Exception('数据表"' . $tableName . '"的字段"' . $columnName . '"被唯一索引使用，建议此列设置为非空字段');
            }
            if('Y' == $column['primary_key']) {
                if('auto_increment' == $column['default_value']) {
                    $extra = 'PUMP_COL_TYPE_INCREMENT';
                    $sequence = $this->getSequenceFromStr($column['next_value']);
                    $columns[$columnName]['sequence'] = $sequence;
                } else if (false != stripos($column['default_value'], COL_TYPE_UUID)) {
                    $extra = 'PUMP_COL_TYPE_UUID';
                }
                $columns[$columnName]['extra'] = $extra;
            }
            if('N' == $column['primary_key']) {
                $default = $this->getDefaultValueFromStr($column['default_value']);
                if($default) $columns[$columnName]['notnull'] = true;
                if('PUMP_COL_TYPE_INCREMENT' != $default) { 
                    $columns[$columnName]['default'] = $default;
                }else{
                    $columns[$columnName]['sequence'] = $this->getSequenceFromStr($column['next_value']);
                }
            }
            if (in_array($columnName, Table::$createTimeArray)) {
                $createAt = $columnName;
            }
            if (in_array($columnName, Table::$updateTimeArray)) {
                $updateAt = $columnName;
            }
        }
        $metadata = array('columns'=>$columns,
                          'create_at'=>$createAt,
                          'update_at'=>$updateAt,
                          'primaryKey'=>$tableKeys['primary'] ? $tableKeys['primary'] : array(),
                          'uniqueKeys'=>$tableKeys['uniqueKeys'] ? $tableKeys['uniqueKeys'] : array(),
                          'indexes'   =>$tableKeys['indexes'] ? $tableKeys['indexes'] : array());
        return $metadata;

    }

    private function getSequenceFromStr($seqStr) {
        preg_match('/nextval\(\'(.+)\'::regclass\)/', $seqStr, $seq);
        return $seq[1];
    }

    private function getDefaultValueFromStr($defaultValue) {
        $ar = explode('::', $defaultValue);
        return str_ireplace(COL_TYPE_UUID, 'PUMP_COL_TYPE_UUID', str_replace('auto_increment', 'PUMP_COL_TYPE_INCREMENT', trim($ar[0], "'")));
    }

    public function getColumnArray($tableName) {
        $strQuery = "SELECT
                    '{$tableName}' AS table_name,
                    attrelid       AS table_id,
                    pg_attribute.attnum AS column_position,
                    pg_attribute.attname AS column_name,
                    pg_type.typname AS column_type,
                    pg_attribute.atttypmod  AS column_length,
                    CASE WHEN pg_attribute.attnotnull THEN 'Y' ELSE 'N' END AS not_null,
                    CASE WHEN pg_attrdef.adsrc LIKE 'nextval%' THEN 'auto_increment' ELSE pg_attrdef.adsrc END AS default_value,
                    CASE WHEN pg_attrdef.adsrc LIKE 'nextval%'  THEN pg_attrdef.adsrc ELSE '' END AS next_value,
                    CASE WHEN pg_attribute.attnum = any(pg_primary.conkey) THEN 'Y' ELSE 'N' END AS primary_key
                FROM pg_attribute
                LEFT JOIN pg_type
                    ON pg_attribute.atttypid=pg_type.oid
                LEFT JOIN pg_attrdef
                    ON pg_attribute.attrelid=pg_attrdef.adrelid
                      AND pg_attribute.attnum=pg_attrdef.adnum
                LEFT JOIN pg_constraint AS pg_primary
                    ON pg_attribute.attrelid = pg_primary.conrelid
                      AND pg_primary.contype='p'
                WHERE attrelid = (SELECT relid FROM pg_statio_user_tables 
                 WHERE relname='{$tableName}')
                    AND pg_attribute.attnum >0
                    AND pg_attribute.attisdropped = FALSE
                ORDER BY pg_attribute.attnum";
        $columnList = $this->getAll($strQuery);
        $columnArray = array();
        foreach($columnList as $column) {
            $columnArray[$column['column_position']] = $column;
        }
        
        return $columnArray;
    }

    public function getTableKeys($tableName, $columnArray) {
        $tableId = $columnArray[1]['table_id'];
        if(false == $tableId) return array();
        $strQuery ="SELECT ui.indexrelname,pi.indisunique,pi.indisprimary,pi.indkey 
                    FROM pg_statio_user_indexes ui
                    LEFT JOIN pg_index pi  ON ui.indexrelid=pi.indexrelid
                    WHERE ui.schemaname='public'
                      AND ui.relid={$tableId}
                    ORDER BY ui.indexrelid";
        $indexList = $this->getAll($strQuery);
        $keys = array();
        foreach($indexList as $index) {
            if($index['indisprimary']) $keyType = 'primary';
            elseif($index['indisunique']) $keyType = 'uniqueKeys';
            else $keyType = 'indexes';

            $columnIndexes = explode(' ',  $index['indkey']);
            $keyColumns = array();
            foreach($columnIndexes as $kIndex) $keyColumns[] = $columnArray[$kIndex]['column_name'];
            if ('primary' == $keyType) {
                $keys[$keyType] = $keyColumns;
            } else {
                $keys[$keyType][$index['indexrelname']] = $keyColumns;
            }
            
        }
        return $keys;
    }

    public function getAllTables() {
        if (0 == sizeof($this->tables)) {
            $tableList = $this->getAll("SELECT relname FROM pg_statio_user_tables WHERE schemaname='public'");
            foreach ($tableList as $row) {
                $this->tables[] = preg_replace('/^' . $this->dbPrefix . '/', '', strtolower($row['relname']));
            }
        }
        return $this->tables;
    }

    public function createTable($tableName, $metadata) {
        $uniqueKeys = $metadata['uniqueKeys'];
        $primaryKey = $metadata['primaryKey'];
        $notnullColumns = array();
        if (is_array($uniqueKeys)) {
            foreach ($uniqueKeys as $keyName => $columns) {
                $notnullColumns = array_merge($notnullColumns, $columns);
            }
            foreach($primaryKey as $column) $notnullColumns[] = $column;
            $notnullColumns = array_unique($notnullColumns);
        }
        $trueTableName = $this->dbPrefix . $tableName;
        $strQuery = 'CREATE TABLE ' . $trueTableName;
        $columns = array();
        foreach ($metadata['columns'] as $colName=>$colSetting) {
            $columnStr = $colName;
            if (false !== stripos($colSetting['type'], 'Number')) {
                $colSetting['tableName'] = $tableName;
                $colSetting['colName'] = $colName;
                $columnStr .= $this->getNumberCreateStr($colSetting);
                $columns[] = $columnStr;
            } else if (false !== stripos($colSetting['type'], 'String')) {
                $columnStr .= $this->getStringCreateStr($colSetting);
                $columns[] = $columnStr;
            } else if (false !== stripos($colSetting['type'], 'date')) {
                $columnStr .= $this->getDatetimeCreateStr($colSetting);
                $columns[] = $columnStr;
            }
        }
        if(sizeof($metadata['primaryKey'])) {
            $columns[] = ' primary key(' . implode(',', $metadata['primaryKey']) . ')';
        }
        if (sizeof($metadata['uniqueKeys'])) {
            foreach($metadata['uniqueKeys'] as $keyName=>$keyCols) {
                $columns[] = ' CONSTRAINT ' . $keyName . ' UNIQUE (' . implode(',', $keyCols) . ')';
            }
        }

        $strQuery .= '(' . implode(',', $columns) . ')';
        if($this->execute($strQuery)) {
            $this->tables[] = $tblName;
            if (sizeof($metadata['indexes'])) {
                foreach($metadata['indexes'] as $keyName=>$keyCols) {
                    $strQuery = 'CREATE index ' . $keyName . ' ON ' . $trueTableName . '(' . implode(',', $keyCols) . ')';
                    $this->execute($strQuery);
                }
            }
            return true;
        } 
        return false;
    }
    
    private function getNumberCreateStr($colSetting) {
        $numLen = trim(str_ireplace('Number', '', $colSetting['type']), '()');
        if(false !== stripos($numLen, ',')) {
            $createStr = ' numeric(' . $numLen . ')';
        } else {
            if($numLen <= 6) $createStr = ' smallint';
            elseif($numLen <=11) $createStr = ' int';
            elseif($numLen <=20) $createStr = ' bigint';
        }

        if($colSetting['notnull'] || '' != $colSetting['default'] || '' != $colSetting['sequence']) {
            $createStr .= ' not null';
        }

        if('' != $colSetting['default']) 
            $createStr .= ' default ' . $colSetting['default'];
        elseif (COL_TYPE_INCREMENT == $colSetting['extra'] || '' != $colSetting['sequence']) {
            $sequence = $colSetting['sequence'];
            if(false == $sequence) 
                $sequence = 'pump_seq_' . $colSetting['tableName'] . '_' . $colSetting['colName'];
            $this->createSequence($sequence);
            $createStr .= " default nextval('" . $sequence . "'::regclass)";
        }
        return $createStr;
    }

    private function getStringCreateStr($colSetting) {
        if(COL_TYPE_UUID == $colSetting['extra'] || COL_TYPE_UUID == $colSetting['default']) {
            $strLen = 36;
        } else {
            $strLen = trim(str_ireplace('String', '', $colSetting['type']), '()');
        }
        if ($strLen) {
            $createStr = ' character varying(' . $strLen . ')';
        } else {
            if(false == $colSetting['fulltext']) {
                $createStr = ' text';
            } else {
                $createStr = ' tsvector';
            }
        }
        if ($colSetting['notnull'] || COL_TYPE_UUID == $colSetting['default'] || '' != $colSetting['extra']) {
            $createStr .= ' not null';
        }

        if ('' != $colSetting['default']) {
            $createStr .= " DEFAULT '" . $colSetting['default'] . "'";
        } else {
            if (COL_TYPE_UUID == $colSetting['extra']) {
                $createStr .= " DEFAULT '" . COL_TYPE_UUID . "'";
            }
        }
        
        return $createStr;
    }

    private function getDatetimeCreateStr($colSetting) {
        if('date' == strtolower($colSetting['type'])) {
            $createStr = ' date';
        } else {
            $createStr = ' timestamp without time zone';
        }
        if($colSetting['notnull'] || '' != $colSetting['default']) {
            $createStr .= ' not null';
        }
        if ('' != $colSetting['default']) {
            $createStr .= " default '" . $colSetting['default'] . "'";
        }
        return $createStr;
    }

    private function createSequence($sequenceName) {
        $sequenceName = strtolower($sequenceName);
        $this->sequences = $this->getAllSequences(); 
        if(false == isset($this->sequences[$sequenceName])) {
            $strQuery = 'CREATE SEQUENCE ' . $sequenceName;
            $this->execute($strQuery);
            $this->sequences[$sequenceName] = $sequenceName;
        }
    }

    private function getAllSequences() {
        if (0 == sizeof($this->sequences)) {
            $strQuery = "SELECT relname FROM pg_statio_user_sequences WHERE schemaname='public'";
            $seqList = $this->getAll($strQuery);
            foreach($seqList as $row) $this->sequences[$row['relname']] = $row['relname'];
        }
        return $this->sequences;
    }

    public function limit($strQuery, $limit) {
        $limitAr = explode(',', $limit);
        if(1 == sizeof($limitAr))
            return $strQuery . ' LIMIT ' . $limit;
        else 
            return $strQuery . ' LIMIT ' . abs(intval($limitAr[1])) . ' OFFSET ' . abs(intval($limitAr[0]));
    }
}
?>